/*
 ============================================================================
 Name		: test.cpp
 Author	  : 
 Copyright   : Your copyright notice
 Description : Exe source file
 ============================================================================
 */
//  Include Files  
#include <stdio.h>
#include <string.h>
#include <time.h>
#include "SQLiteDb.h"
#include <e32base.h>
#include <e32std.h>
#include <e32cons.h>			// Console
#include "CMichatDb.h"
//  Constants
_LIT(KTextConsoleTitle, "Console");
_LIT(KTextFailed, " failed, leave code = %d");
_LIT(KTextPressAnyKey, " [press any key]\n");
//  Global Variables
LOCAL_D CConsoleBase* console; // write all messages to this

#define NUM 500
#define BATCH 100

void test_encapsulation()
{
	unsigned char buf[512];
	SQLiteDbManager * mgr = SQLiteDbManager::GetInstance();
	mgr->Open("C:\\data\\miliao\\test.sqlite");
	mgr->Query("create table test (name TEXT, age INTEGER, weight BLOB)");
	mgr->BeginTransaction();
//	mgr->Execute("insert into test values ('%s', %d, '%f')"
//			, "wanghuan", 25, 23.4);
//	int id = mgr->GetLastRowId();
	SQLiteStatement stmt(mgr->CompileStatement("insert into test values (?, %d, ?)", 20));
	stmt.Bind(1, "zhaoxingxing");
	stmt.Bind(2, (unsigned char * )buf, sizeof(buf));
	stmt.Execute();
	stmt = mgr->CompileStatement("select * from test");
	SQLiteResultSet rs = stmt.Query();
	while (rs.HasNext())
	{
		const char * name = rs.GetString("name");
		int age = rs.GetInt(2);
		rs.Next();
	}
	
	rs = mgr->Query("select * from test where name = '%s'"
			, "wanghuan");
	while (rs.HasNext())
	{
		const char * name = rs.GetString("name");
		int age = rs.GetInt(2);
		double weight = rs.GetFloat(3);
		rs.Next();
	}
	mgr->Execute("update test set age = %d where name = '%s'"
			, 24, "wanghuan");
	int count = mgr->GetAffectedRows();
	mgr->EndTransaction();
	mgr->Close();
	//mgr->Execute("delete from test");
	//count = mgr->GetAffectedRows();
}

void test_dbms()
{
	
	char sql[512];
	int err;
	
    TableDesC td;
    td.SetName("performance_test");
    td.AddColumn("column1", EDbColText8);
    td.AddColumn("column2", EDbColInt32);
    td.AddColumn("column3", EDbColInt32);
    td.AddColumn("column4", EDbColInt32);
    td.AddColumn("column5", EDbColInt32);
    td.AddColumn("column6", EDbColInt32);
    td.AddColumn("column7", EDbColText8);
    td.AddColumn("column8", EDbColReal32);
    td.AddColumn("column9", EDbColText8);
    td.AddColumn("column10", EDbColLongText8);
    CMichatDbManager *mgr = CMichatDbManager::GetInstanceL();
    TRAP(err, mgr->CreateTableL(td));
    
    TTime begin;
	TTime end;
	TTimeIntervalSeconds diff;
	
	begin.HomeTime();
	console->Printf(_L("dbms begin to insert %d records\n"), NUM);
	for (int i = 0; i < NUM / BATCH; i++) {
		mgr->BeginTransaction();
		for (int j = 0; j < BATCH; j++) {
			TableItem ti;
			ti.SetString8(1, YString8("Hallo Welt"));
			ti.SetInt32(2, i * BATCH + j);
			ti.SetInt32(3, 2);
			ti.SetInt32(4, 3);
			ti.SetInt32(5, 4);
			ti.SetInt32(6, 5);
			ti.SetString8(7, YString8("hello"));
			ti.SetReal32(8, 3.723);
			ti.SetString8(9, YString8("2011-4-13 11:20:43"));
			ti.SetString8(10, YString8("this is a long message to test sqlite performance, hold on there, dont let me done, babe"));
			mgr->InsertItemL(td, ti);
		}
		mgr->EndTransaction();
	}
	end.HomeTime();
	end.SecondsFrom(begin, diff);
	console->Printf(_L("time cost for inserting %d records: %d seconds\n"), NUM, diff.Int());

	begin.HomeTime();
	console->Printf(_L("begin to query %d records\n"), NUM);
	for (int i = 0; i < NUM / BATCH; i++) {
		mgr->BeginTransaction();
		for (int j = 0; j < BATCH; j++) {
			sprintf(
				sql,
				"select * from performance_test where column2 = %d", i * BATCH + j);
			mgr->QueryItemL(td, YString(sql));
			
		}
		mgr->EndTransaction();
	}
	end.HomeTime();
	end.SecondsFrom(begin, diff);
	console->Printf(_L("time cost for querying %d records: %d seconds\n"), NUM, diff.Int());


	begin.HomeTime();
	console->Printf(_L("begin to update %d records\n"), NUM);
	for (int i = 0; i < NUM / BATCH; i++) {
		mgr->BeginTransaction();
		for (int j = 0; j < BATCH; j++) {
			TableItem ti;
			ti.SetString8(1, YString8("Hello world"));
			sprintf(
				sql,
				"column2 = %d", i * BATCH + j);
			mgr->UpdateItemL(td, ti, YString(sql));
		}
		mgr->EndTransaction();
	}
	end.HomeTime();
	end.SecondsFrom(begin, diff);
	console->Printf(_L("time cost for updating %d records: %d seconds\n"), NUM, diff.Int());

	begin.HomeTime();
	console->Printf(_L("begin to delete %d records\n"), NUM);
	for (int i = 0; i < NUM / BATCH; i++) {
		mgr->BeginTransaction();
		for (int j = 0; j < BATCH; j++) {
			sprintf(
				sql,
				"column2 = %d", i * BATCH + j);
			mgr->DeleteItemL(td, YString(sql));
		}
		mgr->EndTransaction();
	}
	end.HomeTime();
	end.SecondsFrom(begin, diff);
	console->Printf(_L("time cost for deleting %d records: %d seconds\n"), NUM, diff.Int());
    
}

void test_sqlite()
{
    sqlite3 *db = NULL;
    int result = sqlite3_open("C:\\data\\miliao\\test.sqlite", &db);

    char tmp[512];
    char sql[512];
    char *ptr = tmp;
    sqlite3_stmt *statement = NULL;

    result = sqlite3_exec(db, "pragma synchronous = off", NULL, NULL, &ptr);

    sprintf(
        sql,
        "%s",
        "create table performance_test (column1 TEXT, column2 INTEGER, column3 INTEGER, column4 INTEGER, column5 INTEGER, column6 INTEGER, column7 TEXT, column8 FLOAT, column9 TEXT, column10 TEXT)");
    result = sqlite3_prepare(db, (const char*) sql, (int) strlen(sql), &statement,
        (const char **) &ptr);
    result = sqlite3_step(statement);
    result = sqlite3_finalize(statement);

    TTime begin;
    TTime end;
    TTimeIntervalSeconds diff;

    begin.HomeTime();
    console->Printf(_L("begin to insert %d records\n"), NUM);
    for (int i = 0; i < NUM / BATCH; i++) {
        result = sqlite3_exec(db, "begin transaction", NULL, NULL, &ptr);
        for (int j = 0; j < BATCH; j++) {
            sprintf(
                sql,
                "insert into performance_test values ('Hallo Welt', %d, 2, 3, 4, 5, 'hello', 3.723, '2011-4-13 11:20:43', 'this is a long message to test sqlite performance, hold on there, dont let me done, babe')", i * BATCH + j);
            result = sqlite3_prepare(db, (const char*)sql, (int)strlen(sql), &statement, (const char **)&ptr);
            result = sqlite3_step(statement);
            result = sqlite3_finalize(statement);
        }
        result = sqlite3_exec(db, "commit transaction", NULL, NULL, &ptr);
    }
    end.HomeTime();
    end.SecondsFrom(begin, diff);
    console->Printf(_L("time cost for inserting %d records: %d seconds\n"), NUM, diff.Int());

    begin.HomeTime();
    console->Printf(_L("begin to query %d records\n"), NUM);
    for (int i = 0; i < NUM / BATCH; i++) {
    	result = sqlite3_exec(db, "begin transaction", NULL, NULL, &ptr);
        for (int j = 0; j < BATCH; j++) {
            sprintf(
                sql,
                "select * from performance_test where column2 = %d", i * BATCH + j);
            result = sqlite3_prepare(db, (const char*)sql, (int)strlen(sql), &statement, (const char **)&ptr);
            result = sqlite3_step(statement);
            result = sqlite3_finalize(statement);
        }
        result = sqlite3_exec(db, "commit transaction", NULL, NULL, &ptr);
    }
    end.HomeTime();
    end.SecondsFrom(begin, diff);
    console->Printf(_L("time cost for querying %d records: %d seconds\n"), NUM, diff.Int());


    begin.HomeTime();
    console->Printf(_L("begin to update %d records\n"), NUM);
    for (int i = 0; i < NUM / BATCH; i++) {
    	result = sqlite3_exec(db, "begin transaction", NULL, NULL, &ptr);
        for (int j = 0; j < BATCH; j++) {
            sprintf(
                sql,
                "update performance_test set column1 = 'hello world' where column2 = %d", i * BATCH + j);
            result = sqlite3_prepare(db, (const char*)sql, (int)strlen(sql), &statement, (const char **)&ptr);
            result = sqlite3_step(statement);
            result = sqlite3_finalize(statement);
        }
        result = sqlite3_exec(db, "commit transaction", NULL, NULL, &ptr);
    }
    end.HomeTime();
    end.SecondsFrom(begin, diff);
    console->Printf(_L("time cost for updating %d records: %d seconds\n"), NUM, diff.Int());

    begin.HomeTime();
    console->Printf(_L("begin to delete %d records\n"), NUM);
    for (int i = 0; i < NUM / BATCH; i++) {
    	result = sqlite3_exec(db, "begin transaction", NULL, NULL, &ptr);
        for (int j = 0; j < BATCH; j++) {
            sprintf(
                sql,
                "delete from performance_test where column2 = %d", i * BATCH + j);
            result = sqlite3_prepare(db, (const char*)sql, (int)strlen(sql), &statement, (const char **)&ptr);
            result = sqlite3_step(statement);
            result = sqlite3_finalize(statement);
        }
        result = sqlite3_exec(db, "commit transaction", NULL, NULL, &ptr);
    }
    end.HomeTime();
    end.SecondsFrom(begin, diff);
    console->Printf(_L("time cost for deleting %d records: %d seconds\n"), NUM, diff.Int());

    result = sqlite3_close(db);
}

LOCAL_C void MainL()
{
    console->Write(_L("Begin\n"));
    //test_sqlite();
    //test_dbms();
    test_encapsulation();
    console->Write(_L("End\n"));
}

LOCAL_C void DoStartL()
{
    // Create active scheduler (to run active objects)
    CActiveScheduler* scheduler = new (ELeave) CActiveScheduler();
    CleanupStack::PushL(scheduler);
    CActiveScheduler::Install(scheduler);

    MainL();

    // Delete active scheduler
    CleanupStack::PopAndDestroy(scheduler);
}

//  Global Functions

GLDEF_C TInt E32Main()
{
    // Create cleanup stack
    __UHEAP_MARK;
    CTrapCleanup* cleanup = CTrapCleanup::New();
    // Create output console
    TRAPD(createError, console = Console::NewL(KTextConsoleTitle, TSize(
                KConsFullScreen, KConsFullScreen)));
    if (createError) {
        delete cleanup;
        return createError;
    }
    // Run application code inside TRAP harness, wait keypress when terminated
    TRAPD(mainError, DoStartL());
    if (mainError)
        console->Printf(KTextFailed, mainError);
    console->Printf(KTextPressAnyKey);
    console->Getch();

    delete console;
    delete cleanup;
    __UHEAP_MARKEND;
    return KErrNone;
}

